package jnpf.util;

import cn.hutool.core.util.ObjectUtil;
import cn.hutool.json.JSONUtil;
import jnpf.constant.MsgCode;
import jnpf.enums.CellDataEnum;
import jnpf.exception.DataException;
import jnpf.univer.chart.UniverChartField;
import jnpf.univer.chart.UniverChartModel;
import jnpf.univer.data.cell.UniverDataConfig;
import jnpf.univer.data.custom.UniverCustom;
import jnpf.univer.model.UniverPreview;
import jnpf.univer.model.UniverWorkBook;
import jnpf.univer.sheet.*;
import jnpf.ureport.build.Dataset;
import jnpf.ureport.build.ReportBuilder;
import jnpf.ureport.cell.down.DownCellbuilder;
import jnpf.ureport.cell.right.RightCellbuilder;
import jnpf.ureport.definition.*;
import jnpf.ureport.definition.value.AggregateType;
import jnpf.ureport.definition.value.DatasetValue;
import jnpf.ureport.definition.value.SimpleValue;
import jnpf.ureport.definition.value.Value;
import jnpf.ureport.model.*;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.util.CellReference;

import java.math.BigDecimal;
import java.util.*;
import java.util.function.Function;
import java.util.stream.Collectors;


/**
 * @author ：JNPF开发平台组
 * @version: V3.1.0
 * @copyright 引迈信息技术有限公司
 * @date ：2024/5/11 下午4:35
 */
public class UniverConvert {


    //-------------------------------------解析数据------------------------------------------------

    public UniverPreview transform(String snapshot, String cells, Map<String, List<Map<String, Object>>> dataList) {
        UniverWorkBook univerWorkBook = JsonUtil.getJsonToBean(snapshot, UniverWorkBook.class);
        UniverCustom custom = JsonUtil.getJsonToBean(cells, UniverCustom.class);
        List<UniverSheet> sheetList = new ArrayList<>();
        List<UniverChartModel> chartList = new ArrayList<>();
        for (String order : univerWorkBook.getSheetOrder()) {
            UniverSheet univerSheet = univerWorkBook.getSheets().get(order);
            if (ObjectUtil.isNotEmpty(univerSheet)) {
                List<UniverDataConfig> cellList = custom.getCells().stream().filter(t -> Objects.equals(order, t.getSheet())).collect(Collectors.toList());
                UniverSheet sheet = buildLuckysheetDatas(univerSheet, cellList, dataList, chartList);
                List<UniverDataConfig> echartsList = new ArrayList<>(custom.getEcharts().values()).stream().filter(t -> ObjectUtil.isNotEmpty(t.getSheetDrawingParam()) && Objects.equals(t.getSheetDrawingParam().getSubUnitId(), order)).collect(Collectors.toList());
                chart(chartList, dataList, echartsList);
                sheetList.add(sheet);
            }
        }
        Map<String, UniverSheet> sheet = sheetList.stream().collect(Collectors.toMap(s -> s.getId(), Function.identity()));
        univerWorkBook.setSheets(sheet);
        UniverPreview vo = new UniverPreview();
        vo.setSnapshot(JSONUtil.toJsonStr(univerWorkBook));
        vo.setCells(cells);

        vo.setChartData(JSONUtil.toJsonStr(chartList));
        return vo;
    }

    private UniverSheet buildLuckysheetDatas(UniverSheet sheet, List<UniverDataConfig> cell, Map<String, List<Map<String, Object>>> dataListAll, List<UniverChartModel> chartList) {
        List<UniverDataConfig> datasourceData = cell.stream().filter(t -> CellDataEnum.dataSource.name().equals(t.getType())).collect(Collectors.toList());
        if (datasourceData.isEmpty()) {
            return sheet;
        }
        List<Integer> rowMax = new ArrayList<>();
        rowMax.add(0);
        List<Integer> columnMax = new ArrayList<>();
        columnMax.add(0);
        Map<Integer, Map<Integer, UniverSheetCellData>> cellData = sheet.getCellData();
        for (Integer rowKey : cellData.keySet()) {
            rowMax.add(rowKey);
            Map<Integer, UniverSheetCellData> cellDataMap = cellData.get(rowKey);
            columnMax.addAll(cellDataMap.keySet());
        }
        List<UniverSheetRange> mergeData = sheet.getMergeData();
        Map<Integer, UniverSheetRowData> sheetRowData = sheet.getRowData();
        Map<Integer, UniverSheetColumnData> sheetColumnData = sheet.getColumnData();
        ReportDefinition reportDefinition = new ReportDefinition();
        List<RowDefinition> rows = new ArrayList<>();
        List<ColumnDefinition> columns = new ArrayList<>();
        List<CellDefinition> cells = new ArrayList<>();
        reportDefinition.setRows(rows);
        reportDefinition.setColumns(columns);
        reportDefinition.setCells(cells);
        Map<String, Dataset> datasetMap = new HashMap<>();
        for (String key : dataListAll.keySet()) {
            Dataset dataset = new Dataset();
            dataset.setName(key);
            dataset.setData(dataListAll.get(key));
            datasetMap.put(key, dataset);
        }
        reportDefinition.setDatasetMap(datasetMap);
        Map<String, Functions> functionMap = new HashMap<>();
        for (int i = 0; i <= Collections.max(rowMax); i++) {
            RowDefinition rowDefinition = new RowDefinition();
            rowDefinition.setRowNumber(i);
            rowDefinition.setSheetRowData(sheetRowData.get(i));
            Map<Integer, UniverSheetCellData> columnData = cellData.get(i) != null ? cellData.get(i) : new HashMap<>();
            for (int j = 0; j <= Collections.max(columnMax); j++) {
                ColumnDefinition columnDefinition = new ColumnDefinition();
                columnDefinition.setColumnNumber(j);
                columnDefinition.setSheetColumnData(sheetColumnData.get(j));
                Object styleValue = columnData.get(j) != null && columnData.get(j).getS() != null ? columnData.get(j).getS() : null;
                //合并
                int rowSpan = 0;
                int colSpan = 0;
                StyleModel styleModel = new StyleModel();
                Optional<UniverSheetRange> range = mergeData.stream().filter(t -> t.getStartRow().equals(rowDefinition.getRowNumber()) && t.getStartColumn().equals(columnDefinition.getColumnNumber())).findFirst();
                if (range.isPresent()) {
                    UniverSheetRange sheetRange = range.get();
                    rowSpan = sheetRange.getEndRow() - sheetRange.getStartRow();
                    colSpan = sheetRange.getEndColumn() - sheetRange.getStartColumn();
                    if (rowSpan > 0) {
                        for (int row = sheetRange.getStartRow() + 1; row <= sheetRange.getEndRow() - 1; row++) {
                            Map<Integer, UniverSheetCellData> dataMap = cellData.get(row);
                            if (dataMap != null) {
                                UniverSheetCellData startData = dataMap.get(sheetRange.getStartColumn());
                                if (startData != null) {
                                    styleModel.setL(startData.getS());
                                }
                                UniverSheetCellData endData = dataMap.get(sheetRange.getEndColumn());
                                if (endData != null) {
                                    styleModel.setR(endData.getS());
                                }
                            }
                        }
                    }
                    if (colSpan > 0) {
                        Map<Integer, UniverSheetCellData> startDataMap = cellData.get(sheetRange.getStartRow());
                        Map<Integer, UniverSheetCellData> endDataMap = cellData.get(sheetRange.getEndRow());
                        for (int col = sheetRange.getStartColumn() + 1; col <= sheetRange.getEndColumn() - 1; col++) {
                            if (startDataMap != null) {
                                UniverSheetCellData startData = startDataMap.get(col);
                                if (startData != null) {
                                    styleModel.setT(startData.getS());
                                }
                            }
                            if (endDataMap != null) {
                                UniverSheetCellData endData = endDataMap.get(col);
                                if (endData != null) {
                                    styleModel.setB(endData.getS());
                                }
                            }
                        }
                    }
                    Map<Integer, UniverSheetCellData> startDataMap = cellData.get(sheetRange.getStartRow());
                    if (startDataMap != null) {
                        UniverSheetCellData leftTopData = startDataMap.get(sheetRange.getStartColumn());
                        if (leftTopData != null) {
                            styleModel.setLt(leftTopData.getS());
                        }
                        UniverSheetCellData rightTopData = startDataMap.get(sheetRange.getEndColumn());
                        if (rightTopData != null) {
                            styleModel.setRt(rightTopData.getS());
                        }
                    }
                    Map<Integer, UniverSheetCellData> endDataMap = cellData.get(sheetRange.getEndRow());
                    if (endDataMap != null) {
                        UniverSheetCellData leftBodData = endDataMap.get(sheetRange.getStartColumn());
                        if (leftBodData != null) {
                            styleModel.setLb(leftBodData.getS());
                        }
                        UniverSheetCellData rightBodData = endDataMap.get(sheetRange.getEndColumn());
                        if (rightBodData != null) {
                            styleModel.setRb(rightBodData.getS());
                        }
                    }
                }
                CellDefinition cellDefinition = new CellDefinition();
                cellDefinition.setName(new CellReference(i, j).formatAsString());
                cellDefinition.setRowNumber(i + 1);
                cellDefinition.setColumnNumber(j + 1);
                cellDefinition.setRowSpan(rowSpan > 0 ? rowSpan + 1 : rowSpan);
                cellDefinition.setColSpan(colSpan > 0 ? colSpan + 1 : colSpan);
                cellDefinition.setSheetColumnData(sheetColumnData.get(j));
                cellDefinition.setSheetRowData(sheetRowData.get(i));
                cellDefinition.setUniverStyle(styleValue);
                cellDefinition.setStyleModel(styleModel);

                //判断函数
                String function = columnData.get(j) != null && columnData.get(j).getF() != null ? columnData.get(j).getF().toString() : "";
                if (StringUtil.isNotEmpty(function)) {
                    if (function.startsWith("=")) {
                        int startCount = 0;
                        int endCount = 0;
                        Stack<Integer> stack = new Stack<>();
                        List<String> functionNameList = new ArrayList<>();
                        Map<String, String> functionReplace = new HashMap<>();
                        Map<Integer, Map<Integer, String>> functionCell = new HashMap<>();
                        for (int k = 0; k < function.length(); k++) {
                            char ch = function.charAt(k);
                            if (ch == '(') {
                                startCount++;
                                stack.push(k);
                            }
                            if (ch == ')') {
                                endCount++;
                                if (!stack.isEmpty()) {
                                    int left = stack.pop();
                                    String functionName = function.substring(left, k + 1).replaceAll("\\(", "").replaceAll("\\)", "");
                                    for (String name : functionName.split(",")) {
                                        try {
                                            String[] ranges = name.split(":");
                                            CellReference start = new CellReference(ranges[0]);
                                            CellReference end = new CellReference(ranges.length == 1 ? ranges[0] : ranges[1]);
                                            int startRow = start.getRow();
                                            int startCol = start.getCol();
                                            int endRow = end.getRow();
                                            int endCol = end.getCol();
                                            if (startRow > endRow || (startRow == endRow && startCol > endCol)) {
                                                startRow = end.getRow();
                                                startCol = end.getCol();
                                                endRow = start.getRow();
                                                endCol = start.getCol();
                                            }
                                            List<String> referenceList = new ArrayList<>();
                                            List<CellReference> cellReferenceList = getCellsRange(startRow, endRow, startCol, endCol);
                                            for (CellReference reference : cellReferenceList) {
                                                int row = reference.getRow();
                                                int col = reference.getCol();
                                                Map<Integer, String> rowMap = functionCell.get(row) != null ? functionCell.get(row) : new HashMap<>();
                                                rowMap.put(col, reference.formatAsString());
                                                functionCell.put(row, rowMap);
                                                referenceList.add(reference.formatAsString());
                                            }
                                            if (ranges.length > 1) {
                                                functionReplace.put(name, String.join(",", referenceList));
                                            }
                                        } catch (Exception e) {
                                        }
                                    }
                                }
                            }
                        }
                        List<Integer> rowList = new ArrayList<>(functionCell.keySet()).stream().sorted(Comparator.reverseOrder()).collect(Collectors.toList());
                        for (Integer row : rowList) {
                            Map<Integer, String> rowMap = functionCell.get(row) != null ? functionCell.get(row) : new HashMap<>();
                            List<Integer> colList = new ArrayList<>(rowMap.keySet()).stream().sorted(Comparator.reverseOrder()).collect(Collectors.toList());
                            for (Integer col : colList) {
                                if (rowMap.get(col) != null) {
                                    UniverDataConfig univerCellData = cell.stream().filter(t -> Objects.equals(t.getRow(), row) && Objects.equals(t.getCol(), col)).findFirst().orElse(new UniverDataConfig());
                                    if (Objects.equals(CellDataEnum.text.name(), univerCellData.getType())) {
                                        if (cellData.get(row) != null) {
                                            UniverSheetCellData data = cellData.get(row).get(col);
                                            if (data != null) {
                                                if (data.getF() != null) {
                                                    functionNameList.add(rowMap.get(col));
                                                } else if (data.getV() != null) {
                                                    String name = data.getV().toString();
                                                    function = function.replaceAll(rowMap.get(col), name);
                                                }
                                            }
                                        }
                                    } else {
                                        functionNameList.add(rowMap.get(col));
                                    }
                                }
                            }
                        }
                        if (rowList.isEmpty()) {
                            cellDefinition.setFunctionType(function);
                        }
                        if (Objects.equals(startCount, endCount) && !functionNameList.isEmpty()) {
                            for (String key : functionReplace.keySet()) {
                                function = function.replaceAll(key, functionReplace.get(key));
                            }
                            Functions functions = new Functions();
                            functions.setFunction(function);
                            functions.setName(cellDefinition.getName());
                            functions.setFunctionName(functionNameList);
                            functionMap.put(cellDefinition.getName(), functions);
                            cellDefinition.setFunctionType(function);
                        }
                    }
                }
                //数据类型
                Expand expand = Expand.None;
                String value = columnData.get(j) != null && columnData.get(j).getV() != null ? columnData.get(j).getV().toString() : "";
                Value values = new SimpleValue(value);
                UniverDataConfig univerCellData = cell.stream().filter(t -> Objects.equals(t.getRow(), rowDefinition.getRowNumber()) && Objects.equals(t.getCol(), columnDefinition.getColumnNumber())).findFirst().orElse(new UniverDataConfig());
                UniverDataConfig univerDataConfig = univerCellData.getCustom() != null ? univerCellData.getCustom() : new UniverDataConfig();
                if (Objects.equals(CellDataEnum.dataSource.name(), univerCellData.getType())) {
                    String datasetName = "";
                    String property = "";
                    String fillDirection = univerDataConfig.getFillDirection();
                    String fileName = univerDataConfig.getField();
                    String summaryType = univerDataConfig.getSummaryType();
                    if (StringUtils.isNotEmpty(fileName)) {
                        String[] params = fileName.split("\\.");
                        if (params.length == 2) {
                            datasetName = params[0];
                            property = params[1];
                        }
                    }
                    AggregateType aggregate = AggregateType.none;
                    Integer type = univerDataConfig.getPolymerizationType();
                    if (Objects.equals(type, 1) || Objects.equals(type, 2)) {
                        expand = Objects.equals("portrait", fillDirection) ? Expand.Down : Expand.Right;
                        aggregate = Objects.equals(type, 2) ? AggregateType.group : AggregateType.select;
                    } else if (Objects.equals(type, 3)) {
                        aggregate = AggregateType.value(summaryType);
                    }
                    DatasetValue datasetValue = new DatasetValue();
                    datasetValue.setDatasetName(datasetName);
                    datasetValue.setProperty(property);
                    datasetValue.setAggregate(aggregate);
                    values = datasetValue;
                }

                String leftName = "";
                String leftType = univerDataConfig.getLeftParentCellType();
                if (StringUtil.isNotEmpty(leftType)) {
                    switch (leftType) {
                        case "none":
                            leftName = "root";
                            break;
                        case "custom":
                            leftName = univerDataConfig.getLeftParentCellCustomRowName() + univerDataConfig.getLeftParentCellCustomColName();
                            break;
                    }
                }
                cellDefinition.setLeftParentCellName(leftName);
                String topName = "";
                String topType = univerDataConfig.getTopParentCellType();
                if (StringUtil.isNotEmpty(topType)) {
                    switch (topType) {
                        case "none":
                            topName = "root";
                            break;
                        case "custom":
                            topName = univerDataConfig.getTopParentCellCustomRowName() + univerDataConfig.getTopParentCellCustomColName();
                            break;
                    }
                }
                cellDefinition.setTopParentCellName(topName);

                cellDefinition.setExpand(expand);
                cellDefinition.setValue(values);
                cellDefinition.setP(columnData.get(j) != null && columnData.get(j).getP() != null ? columnData.get(j).getP() : null);
                boolean notAdd = mergeData.stream().filter(t -> t.getStartRow() <= rowDefinition.getRowNumber() && t.getStartColumn() <= columnDefinition.getColumnNumber() && t.getEndRow() >= rowDefinition.getRowNumber() && t.getEndColumn() >= columnDefinition.getColumnNumber()).count() > 0;
                if (!notAdd || mergeData.isEmpty() || range.isPresent()) {
                    cells.add(cellDefinition);
                }
                columnDefinition.setColumnNumber(columnDefinition.getColumnNumber() + 1);
                if (columns.stream().filter(t -> Objects.equals(t.getColumnNumber(), columnDefinition.getColumnNumber())).count() == 0) {
                    columns.add(columnDefinition);
                }
            }
            rowDefinition.setRowNumber(rowDefinition.getRowNumber() + 1);
            rows.add(rowDefinition);
        }
        rebuild(reportDefinition);
        rebuildReportDefinition(reportDefinition);
        ReportBuilder reportBuilder = new ReportBuilder();
        Report report = reportBuilder.buildReport(reportDefinition);
        UniverSheet result = sheet(sheet, report, functionMap);
        return result;
    }

    private UniverSheet sheet(UniverSheet sheet, Report report, Map<String, Functions> functionMap) {
        sheet.setCellData(new HashMap<>());
        sheet.setMergeData(new ArrayList<>());
        sheet.setRowData(new HashMap<>());
        sheet.setColumnData(new HashMap<>());
        Map<Integer, UniverSheetRowData> rowData = new HashMap<>();
        Map<Integer, UniverSheetColumnData> columnData = new HashMap<>();
        UniverSheet result = JsonUtil.getJsonToBean(sheet, UniverSheet.class);
        List<Row> rows = report.getRows();
        List<Column> columns = report.getColumns();
        Map<Row, Map<Column, Cell>> cellMap = report.getRowColCellMap();
        Map<Integer, Map<Integer, UniverSheetCellData>> cellData = new HashMap<>();
        Map<Integer, Map<Integer, Cell>> cellFunctionMap = new HashMap<>();
        List<UniverSheetRange> mergeData = new ArrayList<>();
        //绘制格子
        for (int i = 0; i < rows.size(); i++) {
            Row row = rows.get(i);
            rowData.put(i, row.getSheetRowData());
            for (int j = 0; j < columns.size(); j++) {
                Column col = columns.get(j);
                Cell cell = null;
                if (cellMap.containsKey(row)) {
                    Map<Column, Cell> colMap = cellMap.get(row);
                    if (colMap.containsKey(col)) {
                        cell = colMap.get(col);
                    }
                }
                if (cell == null) {
                    continue;
                }
                cell.setReference(new CellReference(i, j));
                //获取函数格子
                String cellName = cell.getName();
                if (functionMap.get(cellName) != null) {
                    Map<Integer, Cell> data = cellFunctionMap.get(i) != null ? cellFunctionMap.get(i) : new HashMap<>();
                    data.put(j, cell);
                    cellFunctionMap.put(i, data);
                }

                //合并格子
                int startRow = i;
                int endRow = i + (cell.getRowSpan() > 0 ? cell.getRowSpan() - 1 : cell.getRowSpan());
                int startCol = j;
                int endCol = j + (cell.getColSpan() > 0 ? cell.getColSpan() - 1 : cell.getColSpan());
                int rowSpan = cell.getRowSpan();
                int colSpan = cell.getColSpan();
                StyleModel styleModel = cell.getStyleModel();
                if (colSpan > 0 || rowSpan > 0) {
                    //左、右边框
                    boolean isL = ObjectUtil.isNotEmpty(styleModel.getL());
                    boolean isR = ObjectUtil.isNotEmpty(styleModel.getR());
                    for (int rowCount = startRow; rowCount <= endRow; rowCount++) {
                        Map<Integer, UniverSheetCellData> dataMap = cellData.get(rowCount) != null ? cellData.get(rowCount) : new HashMap<>();
                        UniverSheetCellData startData = dataMap.get(startCol) != null ? dataMap.get(startCol) : new UniverSheetCellData();
                        startData.setS(isL ? styleModel.getL() : cell.getUniverStyle());
                        dataMap.put(startCol, startData);

                        UniverSheetCellData endData = dataMap.get(endCol) != null ? dataMap.get(endCol) : new UniverSheetCellData();
                        endData.setS(isR ? styleModel.getR() : cell.getUniverStyle());
                        dataMap.put(endCol, endData);

                        cellData.put(rowCount, dataMap);
                    }

                    //上、下边框
                    boolean isT = ObjectUtil.isNotEmpty(styleModel.getT());
                    boolean isB = ObjectUtil.isNotEmpty(styleModel.getB());
                    for (int columnCount = startCol; columnCount <= endCol; columnCount++) {
                        Map<Integer, UniverSheetCellData> startDataMap = cellData.get(startRow) != null ? cellData.get(startRow) : new HashMap<>();
                        UniverSheetCellData startData = startDataMap.get(columnCount) != null ? startDataMap.get(columnCount) : new UniverSheetCellData();
                        startData.setS(isT ? styleModel.getT() : cell.getUniverStyle());
                        startDataMap.put(columnCount, startData);
                        cellData.put(startRow, startDataMap);

                        Map<Integer, UniverSheetCellData> endDataMap = cellData.get(endRow) != null ? cellData.get(endRow) : new HashMap<>();
                        UniverSheetCellData endData = endDataMap.get(columnCount) != null ? endDataMap.get(columnCount) : new UniverSheetCellData();
                        endData.setS(isB ? styleModel.getB() : cell.getUniverStyle());
                        endDataMap.put(columnCount, endData);
                        cellData.put(endRow, endDataMap);
                    }

                    //角样式
                    boolean isLT = ObjectUtil.isNotEmpty(styleModel.getLt());
                    boolean isRT = ObjectUtil.isNotEmpty(styleModel.getRt());
                    Map<Integer, UniverSheetCellData> startDataMap = cellData.get(startRow) != null ? cellData.get(startRow) : new HashMap<>();
                    UniverSheetCellData leftTopData = startDataMap.get(startCol) != null ? startDataMap.get(startCol) : new UniverSheetCellData();
                    leftTopData.setS(isLT ? styleModel.getLt() : cell.getUniverStyle());
                    startDataMap.put(startCol, leftTopData);
                    UniverSheetCellData rightTopData = startDataMap.get(endCol) != null ? startDataMap.get(endCol) : new UniverSheetCellData();
                    rightTopData.setS(isRT ? styleModel.getRt() : cell.getUniverStyle());
                    startDataMap.put(endCol, rightTopData);
                    cellData.put(startRow, startDataMap);

                    boolean isLB = ObjectUtil.isNotEmpty(styleModel.getLb());
                    boolean isRB = ObjectUtil.isNotEmpty(styleModel.getRb());
                    Map<Integer, UniverSheetCellData> endDataMap = cellData.get(endRow) != null ? cellData.get(endRow) : new HashMap<>();
                    UniverSheetCellData leftBottomData = endDataMap.get(startCol) != null ? endDataMap.get(startCol) : new UniverSheetCellData();
                    leftBottomData.setS(isLB ? styleModel.getLb() : cell.getUniverStyle());
                    endDataMap.put(startCol, leftBottomData);
                    UniverSheetCellData rightBottomData = endDataMap.get(endCol) != null ? endDataMap.get(endCol) : new UniverSheetCellData();
                    rightBottomData.setS(isRB ? styleModel.getRb() : cell.getUniverStyle());
                    endDataMap.put(endCol, rightBottomData);
                    cellData.put(endRow, endDataMap);

                    boolean notRange = mergeData.stream().filter(t -> t.getStartRow() <= startRow && t.getStartColumn() <= startCol && t.getEndRow() >= startRow && t.getEndColumn() >= startCol).count() > 0;
                    if (!notRange) {
                        UniverSheetRange range = new UniverSheetRange();
                        range.setStartRow(startRow);
                        range.setEndRow(endRow);
                        range.setStartColumn(startCol);
                        range.setEndColumn(endCol);
                        mergeData.add(range);
                    }
                }
                //赋值
                UniverSheetCellData data = new UniverSheetCellData();
                data.setS(cell.getUniverStyle());
                data.setF(cell.getFunctionType());
                data.setP(cell.getP());
                if (cell.getData() != null) {
                    Object sheetData = cell.getData();
                    Integer dataType = 1;
                    try {
                        sheetData = new BigDecimal(String.valueOf(sheetData));
                        dataType = 2;
                    } catch (Exception e) {
                    }
                    if (cell.getData() instanceof Boolean) {
                        dataType = 3;
                    }
                    if (ObjectUtil.isNotEmpty(cell.getFunctionType())) {
                        dataType = 4;
                    }
                    data.setT(dataType);
                    data.setV(sheetData);
                }
                columnData.put(j, col.getSheetColumnData());
                Map<Integer, UniverSheetCellData> cellDataMap = cellData.get(i) != null ? cellData.get(i) : new HashMap<>();
                cellDataMap.put(j, data);
                cellData.put(i, cellDataMap);
            }
        }
        //修改函数
        List<Integer> rowList = new ArrayList<>(cellFunctionMap.keySet()).stream().sorted(Comparator.naturalOrder()).collect(Collectors.toList());
        for (Integer row : rowList) {
            Map<Integer, Cell> rowMap = cellFunctionMap.get(row) != null ? cellFunctionMap.get(row) : new HashMap<>();
            List<Integer> colList = new ArrayList<>(rowMap.keySet()).stream().sorted(Comparator.naturalOrder()).collect(Collectors.toList());
            for (Integer col : colList) {
                Cell cell = rowMap.get(col);
                Functions model = functionMap.get(cell.getName());
                if (model != null) {
                    model.setRowNumber(row);
                    model.setColNumber(col);
                    functionList(cell, model, report);
                    //赋值
                    UniverSheetCellData data = new UniverSheetCellData();
                    data.setS(cell.getUniverStyle());
                    data.setF(cell.getFunctionType());
                    data.setP(cell.getP());
                    if (cell.getData() != null) {
                        Object sheetData = cell.getData();
                        Integer dataType = 4;
                        data.setT(dataType);
                        data.setV(sheetData);
                    }
                    Map<Integer, UniverSheetCellData> cellDataMap = cellData.get(row) != null ? cellData.get(row) : new HashMap<>();
                    cellDataMap.put(col, data);
                    cellData.put(row, cellDataMap);
                }
            }
        }

        result.setCellData(cellData);
        result.setMergeData(mergeData);
        result.setRowData(rowData);
        result.setColumnData(columnData);
        List<Integer> rowMax = new ArrayList<>();
        rowMax.add(sheet.getRowCount());
        rowMax.add(rows.size());
        result.setRowCount(Collections.max(rowMax));
        List<Integer> columnMax = new ArrayList<>();
        columnMax.add(sheet.getColumnCount());
        columnMax.add(columns.size());
        result.setColumnCount(Collections.max(columnMax));
        return result;
    }

    private void functionList(Cell cell, Functions model, Report report) {
        String function = new String(model.getFunction());
        Map<String, List<Cell>> cellsMap = report.getCellsMap();
        int endRow = model.getRowNumber();
        int endCol = model.getColNumber();
        Map<String, List<String>> functionName = new LinkedHashMap<>();
        for (String name : model.getFunctionName()) {
            Map<String, List<String>> leftTop = model.getLeftTop();
            Map<String, List<String>> leftBottom = model.getLeftBottom();
            Map<String, List<String>> rightTop = model.getRightTop();
            Map<String, List<String>> rightBottom = model.getRightBottom();
            List<String> leftTopListAll = leftTop.get(name) != null ? leftTop.get(name) : new ArrayList<>();
            List<String> leftBottomListAll = leftBottom.get(name) != null ? leftBottom.get(name) : new ArrayList<>();
            List<String> rightTopListAll = rightTop.get(name) != null ? rightTop.get(name) : new ArrayList<>();
            List<String> rightBottomListAll = rightBottom.get(name) != null ? rightBottom.get(name) : new ArrayList<>();
            List<String> leftTopList = new ArrayList<>();
            List<String> leftBottomList = new ArrayList<>();
            List<String> rightTopList = new ArrayList<>();
            List<String> rightBottomList = new ArrayList<>();
            List<Cell> cellList = cellsMap.get(name) != null ? cellsMap.get(name) : new ArrayList<>();
            for (Cell functionCell : cellList) {
                CellReference reference = functionCell.getReference();
                if (functionCell.getReference() != null) {
                    int functionRow = reference.getRow();
                    int functionCol = reference.getCol();
                    String format = reference.formatAsString();
                    //左上角
                    if (endRow >= functionRow && endCol >= functionCol) {
                        if (!leftTopListAll.contains(format)) {
                            leftTopList.add(format);
                        }
                    }
                    //左下角
                    if (endRow < functionRow && endCol >= functionCol) {
                        if (!leftBottomListAll.contains(format)) {
                            leftBottomList.add(format);
                        }
                    }
                    //右上角
                    if (endRow > functionRow) {
                        if (!rightTopListAll.contains(format)) {
                            rightTopList.add(format);
                        }
                    }
                    //右下角
                    if (endRow < functionRow || (endRow == functionRow && endCol >= functionCol)) {
                        if (!rightBottomListAll.contains(format)) {
                            rightBottomList.add(format);
                        }
                    }
                }
            }
            List<String> nameList = functionName.get(name) != null ? functionName.get(name) : new ArrayList<>();
            if (leftTopList.size() > 0) {
                leftTopListAll.addAll(leftTopList);
                nameList.addAll(leftTopList);
                leftTop.put(name, leftTopListAll);
            } else if (rightTopList.size() > 0) {
                rightTopListAll.addAll(rightTopList);
                nameList.addAll(rightTopList);
                rightTop.put(name, rightTopListAll);
            } else if (leftBottomList.size() > 0) {
                leftBottomListAll.addAll(leftBottomList);
                nameList.addAll(leftBottomList);
                leftBottom.put(name, leftBottomListAll);
            } else if (rightBottomList.size() > 0) {
                rightBottomListAll.addAll(rightBottomList);
                nameList.addAll(rightBottomList);
                rightBottom.put(name, rightBottomListAll);
            } else {
                if (cellList.size() == 1) {
                    nameList.add(cellList.get(0).getReference().formatAsString());
                }
            }
            if (nameList.size() > 0) {
                functionName.put(name, nameList);
            }
        }
        if (!functionName.isEmpty()) {
            for (String name : functionName.keySet()) {
                List<String> functionNameList = functionName.get(name);
                if (functionNameList.size() > 255) {
                    function = function.replaceAll(name, functionNameList.get(0) + ":" + functionNameList.get(functionNameList.size() - 1));
                } else {
                    function = function.replaceAll(name, String.join(",", functionNameList)).replaceAll(":", ",");
                }
            }
            cell.setFunctionType(function);
        }
    }

    private void chart(List<UniverChartModel> chartList, Map<String, List<Map<String, Object>>> dataListAll, List<UniverDataConfig> cellList) {
        for (UniverDataConfig model : cellList) {
            if (model.getSheetDrawingParam() != null) {
                UniverDataConfig cell = model.getSheetDrawingParam();
                UniverChartModel chartModel = new UniverChartModel();
                chartModel.setDrawingId(cell.getDrawingId());
                chartModel.setSubUnitId(cell.getSubUnitId());
                chartModel.setUnitId(cell.getUnitId());
                UniverDataConfig config = model.getOption() != null ? model.getOption() : new UniverDataConfig();
                //数据
                List<Map<String, Object>> list = new ArrayList<>();
                String classifyNameField = config.getClassifyNameField();
                if (StringUtil.isNotEmpty(classifyNameField)) {
                    String[] params = classifyNameField.split("\\.");
                    if (params.length == 2 && dataListAll.get(params[0]) != null) {
                        list.addAll(dataListAll.get(params[0]));
                        config.setClassifyNameField(params[1]);
                    }
                }
                String seriesNameField = config.getSeriesNameField();
                if (StringUtil.isNotEmpty(seriesNameField)) {
                    String[] params = seriesNameField.split("\\.");
                    if (params.length == 2 && dataListAll.get(params[0]) != null) {
                        list.addAll(dataListAll.get(params[0]));
                        config.setSeriesNameField(params[1]);
                    }
                }
                String seriesDataField = config.getSeriesDataField();
                if (StringUtil.isNotEmpty(seriesDataField)) {
                    String[] params = seriesDataField.split("\\.");
                    if (params.length == 2 && dataListAll.get(params[0]) != null) {
                        list.addAll(dataListAll.get(params[0]));
                        config.setSeriesDataField(params[1]);
                    }
                }
                List<Map<String, Object>> dataList = new ArrayList<>(new HashSet<>(list));
                if (dataList.isEmpty()){
                    continue;
                }
                UniverChartField chartField = ChartUtil.chart(dataList, config);
                chartModel.setField(chartField);
                chartList.add(chartModel);
            }
        }
    }

    private void rebuild(ReportDefinition report) {
        List<CellDefinition> cells = report.getCells();
        Map<String, CellDefinition> cellsMap = new HashMap<>();
        Map<String, CellDefinition> cellsRowColMap = new HashMap<>();
        for (CellDefinition cell : cells) {
            cellsMap.put(cell.getName(), cell);
            int rowNum = cell.getRowNumber();
            int colNum = cell.getColumnNumber();
            int rowSpan = cell.getRowSpan() > 0 ? cell.getRowSpan() : 1;
            int colSpan = cell.getColSpan() > 0 ? cell.getColSpan() : 1;
            int rowStart = rowNum;
            int rowEnd = rowNum + rowSpan;
            int colStart = colNum;
            int colEnd = colNum + colSpan;
            for (int i = rowStart; i < rowEnd; i++) {
                cellsRowColMap.put(i + "," + colNum, cell);
            }
            for (int i = colStart; i < colEnd; i++) {
                cellsRowColMap.put(rowNum + "," + i, cell);
            }
        }
        for (CellDefinition cell : cells) {
            int rowNumber = cell.getRowNumber();
            int colNumber = cell.getColumnNumber();
            Value value = cell.getValue();
            String leftParentCellName = cell.getLeftParentCellName();
            if (StringUtil.isNotEmpty(leftParentCellName)) {
                CellDefinition targetCell = cellsMap.get(leftParentCellName);
                cell.setLeftParentCell(targetCell);
            } else {
                if (colNumber > 1) {
                    CellDefinition targetCell = cellsRowColMap.get(rowNumber + "," + (colNumber - 1));
                    if (value instanceof DatasetValue) {
                        for (int i = colNumber; i > 0; i--) {
                            CellDefinition definition = cellsRowColMap.get(rowNumber + "," + (i - 1));
                            if (definition != null && definition.getValue() instanceof DatasetValue) {
                                targetCell = definition;
                                break;
                            }
                        }
                    }
                    cell.setLeftParentCell(targetCell);
                }
            }

            String topParentCellName = cell.getTopParentCellName();
            if (StringUtils.isNotBlank(topParentCellName)) {
                CellDefinition targetCell = cellsMap.get(topParentCellName);
                cell.setTopParentCell(targetCell);
            } else {
                if (rowNumber > 1) {
                    CellDefinition targetCell = cellsRowColMap.get((rowNumber - 1) + "," + colNumber);
                    if (value instanceof DatasetValue) {
                        for (int i = rowNumber; i > 0; i--) {
                            CellDefinition definition = cellsRowColMap.get((i - 1) + "," + colNumber);
                            if (definition != null && definition.getValue() instanceof DatasetValue) {
                                targetCell = definition;
                                break;
                            }
                        }
                    }
                    cell.setTopParentCell(targetCell);
                }
            }
        }
    }

    private void rebuildReportDefinition(ReportDefinition reportDefinition) {
        try {
            List<CellDefinition> cells = reportDefinition.getCells();
            for (CellDefinition cell : cells) {
                addRowChildCell(cell, cell);
                addColumnChildCell(cell, cell);
            }
            for (CellDefinition cell : cells) {
                Expand expand = cell.getExpand();
                switch (expand) {
                    case Down:
                        DownCellbuilder downCellbuilder = new DownCellbuilder();
                        downCellbuilder.buildParentCell(cell, cells);
                        break;
                    case Right:
                        RightCellbuilder rightCellbuilder = new RightCellbuilder();
                        rightCellbuilder.buildParentCell(cell, cells);
                        break;
                }
            }
        } catch (Exception e) {
            throw new DataException(MsgCode.FA106.get());
        }
    }

    private static void addRowChildCell(CellDefinition cell, CellDefinition childCell) {
        CellDefinition leftCell = cell.getLeftParentCell();
        if (leftCell == null) {
            return;
        }
        List<CellDefinition> childrenCells = leftCell.getRowChildrenCells();
        childrenCells.add(childCell);
        addRowChildCell(leftCell, childCell);
    }

    private static void addColumnChildCell(CellDefinition cell, CellDefinition childCell) {
        CellDefinition topCell = cell.getTopParentCell();
        if (topCell == null) {
            return;
        }
        List<CellDefinition> childrenCells = topCell.getColumnChildrenCells();
        childrenCells.add(childCell);
        addColumnChildCell(topCell, childCell);
    }

    private static List<CellReference> getCellsRange(int startRow, int endRow, int startCol, int endCol) {
        List<CellReference> referenceList = new ArrayList<>();
        if (startRow >= 0) {
            for (int rowNum = startRow; rowNum <= endRow; rowNum++) {
                for (int colNum = startCol; colNum <= endCol; colNum++) {
                    CellReference reference = new CellReference(rowNum, colNum);
                    referenceList.add(reference);
                }
            }
        }
        return referenceList;
    }

}
